-- =============================================
-- Procedure: GetUser
-- Purpose:	Get a user by their username
-- Author: Dale McFarlane
-- Created: 2013-1-20
-- Modification History
-- Modified By	Modification Date	Reason
-- =====================================================================================
--  
-- =====================================================================================

-- EXEC 

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[GetUser]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
BEGIN
	DROP PROCEDURE dbo.[GetUser]
END
GO

CREATE PROCEDURE GetUser
@username varchar(50)

AS
BEGIN
	SELECT 
	u.user_id,
	u.username,
	u.first_name,
	u.last_name,
	u.email_address,
	u.password,
	u.created_date,
	r.role_id,
	r.name as 'role_name',
	r.description AS 'role_description'
	FROM dbo.[user] u
	LEFT OUTER JOIN user_role ur
		ON u.user_id = ur.user_id
	LEFT OUTER JOIN role r
		ON r.role_id = ur.role_id 
	WHERE 
		u.username = @username 

END
GO